As we dive into the data for the capstone project, the first task
is to understand the conceptual schema. There are two parts to the schema. The part shown on this slide is an entity
relationship diagram, ERD, showing a set of relational tables that are connected by
primary key, foreign key relationships. The users table captures some
basic information about each user. The user ID is the primary key and
uniquely identifies each user. The Twitter attribute is
the Twitter handle of the user. The tags attribute is
a set of key-value pairs. The keys indicate whether they're clicking
more on ads that show up during games and the values represent how active
the user is in this period. The timestamp specifies when
the user's account was created. The Team and the TeamAssignment tables
keeps information about the teams. The Team table captures general
information about the teams, including when they were formed and
their current levels. The strength attribute reflects how
well a team is playing as a whole. On the other hand,
the TeamAssignment table records which user is assigned to which team and
when the user joined that team. As you know, a team that performs
well can move up to a new level. This level movement is maintained
in the LevelEvents table. The User_Session table keeps track of
a user's playing sessions which records which users are playing during a session. And in every session a user
plays at a specific game level using a specific platform. The last three tables are about the user
behavior in the course of a game. Where the users click on the game or
an ad. Or to purchase a game related item. Each such activity has it's own ID and
captures the session, at the time of the event. >> Each table,
also has a couple of specific attributes. For example, the GameClicks table has
an Is_hit attribute which records if the user clicks actually hit
the correct target from Flamingo. The AdClicks table records the id and the
category of the ad the user clicked on. >> Similarly,
the InAppPurchases table has the ID and the price of the item the user
purchased during a game session. The second part of the game models
the chatting activity of active users. In this case we use a grab reader model. The four entity types, namely,
user team, team chat session, and chat item correspond to node
labels shown in different colors. The users and teams get their IDs from the
schema that we saw on the previous slide. Team chat sessions are created where
a user creates a new chat session for a new team. We assume that one team will have
only one chat window at a given time. A chat item is the actual chat. Pretty much like a Tweet, usually, text box with an optional
ability to mention another user. However, we decided not to include the
text in this analysis because we do not want to analyze the messages for
this project. The edges of this graph
represent the different actions that can be taken by a user. A user can create a session for a team,
create a new chat in the session, may join or leave a chat session,
and may mention another user. In this model, we can also send our
response to a previous chat item. This, as you can imagine,
will create threads of conversation. A chat session is owned by a team, and a specific chat item will be
part of that chat session. Each user action here has a timestamp,
as you can see, the highlighted joins
edge has a timestamp. We modeled the timestamp
as seconds elapsed from a specific reference time point. All our analysis in the capstone project
will be based in the Diagram and this graph schema.